package com.ddxz.tool.sql.generator.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ddxz.tool.core.convert.CollectionUtil;
import com.ddxz.tool.core.convert.StringUtil;
import com.ddxz.tool.sql.exception.DdxzSqlException;
import com.ddxz.tool.sql.generator.SqlGenerator;
import com.ddxz.tool.sql.struct.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import static com.ddxz.tool.sql.exception.SqlErrorStatus.*;
import static com.ddxz.tool.sql.struct.DdxzOperatorType.*;
import static com.ddxz.tool.sql.struct.SqlConstData.*;

/**
 * 生成SQL语句
 * @author 朱宗刚
 * @date 2019年10月23日
 * @version 1.0.0
 */

public abstract class BaseSqlGenerator implements SqlGenerator {
    protected BaseSqlGenerator() {}

    @Override
    public String getResult(JSONArray result, JSONArray statistic) {
        if (CollectionUtil.isEmpty(result) && CollectionUtil.isEmpty(statistic)) {
            logger.error(NO_RESULT_FOUND.getMessage());
            throw new DdxzSqlException(NO_RESULT_FOUND);
        }

        StringBuilder builder = new StringBuilder(SqlConstData.SQL_SELECT);
        Set<String> aliasSet = new HashSet<>();
        String alias;
        JSONObject attr;

        for (Object obj : result) {
            attr = (JSONObject) obj;
            alias = StringUtil.isEmpty(attr.getString(SqlConstData.KEY_ALIAS)) ? attr.getString(SqlConstData.KEY_COLUMN)
                    : attr.getString(SqlConstData.KEY_ALIAS);
            builder.append(getLeftQuote())
                    .append(attr.getString(SqlConstData.KEY_COLUMN))
                    .append(getRightQuote())
                    .append(SqlConstData.SQL_ALIAS)
                    .append(getLeftQuote())
                    .append(alias)
                    .append(getRightQuote());
            if (aliasSet.contains(alias)) {
                logger.error(REPEAT_ALIAS.getMessage(), alias);
                throw new DdxzSqlException(REPEAT_ALIAS, alias);
            } else {
                aliasSet.add(alias);
            }
            builder.append(SqlConstData.SQL_DELIMITER);
        }

        if (CollectionUtil.isEmpty(statistic)) {
            if (builder.length() <= SqlConstData.SQL_SELECT.length()) {
                logger.error(NO_RESULT_FOUND.getMessage());
                throw new DdxzSqlException(NO_RESULT_FOUND);
            }
        }

        for (Object obj : statistic) {
            attr = (JSONObject) obj;
            alias = DdxzAggregateType.getTypeByCode(attr.getInteger(SqlConstData.SQL_AGG_TYPE)).getValue().toLowerCase();
            builder.append(DdxzAggregateType.getTypeByCode(attr.getInteger(SqlConstData.SQL_AGG_TYPE)).getValue())
                    .append(getLeftQuote())
                    .append(attr.getString(SqlConstData.KEY_COLUMN))
                    .append(getRightQuote())
                    .append(SqlConstData.SQL_AGG_END)
                    .append(SqlConstData.SQL_ALIAS)
                    .append(getLeftQuote());
            alias = StringUtil.isEmpty(attr.getString(SqlConstData.KEY_ALIAS))
                    ? alias + attr.getString(SqlConstData.KEY_COLUMN)
                    : attr.getString(SqlConstData.KEY_ALIAS);
            builder.append(alias)
                    .append(getRightQuote());

            if (aliasSet.contains(alias)) {
                logger.error(REPEAT_ALIAS.getMessage(), alias);
                throw new DdxzSqlException(REPEAT_ALIAS, alias);
            } else {
                aliasSet.add(alias);
            }
            builder.append(SqlConstData.SQL_DELIMITER);
        }

        if (builder.length() <= SqlConstData.SQL_SELECT.length()) {
            logger.error(NO_RESULT_FOUND.getMessage());
            throw new DdxzSqlException(NO_RESULT_FOUND);
        }

        return builder.delete(builder.lastIndexOf(SqlConstData.SQL_DELIMITER), builder.length()).toString();
    }

    @Override
    public String getOrder(JSONArray order) {
        if (CollectionUtil.isEmpty(order)) {
            return "";
        }

        StringBuilder builder = new StringBuilder(SqlConstData.SQL_ORDER);
        JSONObject attr;
        for (Object obj : order) {
            attr = (JSONObject) obj;
            builder.append(getLeftQuote())
                    .append(attr.getString(SqlConstData.KEY_COLUMN))
                    .append(getRightQuote())
                    .append(DdxzSortType.getTypeByCode(attr.getInteger(SqlConstData.SQL_SORT_TYPE)).getValue())
                    .append(SqlConstData.SQL_DELIMITER);
        }

        return builder.delete(builder.lastIndexOf(SqlConstData.SQL_DELIMITER), builder.length()).toString();
    }

    @Override
    public String getGroup(JSONArray result) {
        if (CollectionUtil.isEmpty(result)) {
            return "";
        }

        StringBuilder builder = new StringBuilder(SqlConstData.SQL_GROUP);
        JSONObject attr;
        for (Object obj : result) {
            attr = (JSONObject) obj;
            builder.append(getLeftQuote())
                    .append(attr.getString(SqlConstData.KEY_COLUMN))
                    .append(getRightQuote())
                    .append(SqlConstData.SQL_DELIMITER);
        }
        return builder.delete(builder.lastIndexOf(SqlConstData.SQL_DELIMITER), builder.length()).toString();
    }

    @Override
    public String getCondition(JSONArray condition) {
        if (CollectionUtil.isEmpty(condition)) {
            return "";
        }

        StringBuilder builder = new StringBuilder(SqlConstData.SQL_WHERE);
        JSONObject attr;
        for (Object obj : condition) {
            attr = (JSONObject) obj;
            builder.append(getLeftQuote())
                    .append(attr.getString(SqlConstData.KEY_COLUMN))
                    .append(getRightQuote());
            int oper = attr.getInteger(SqlConstData.SQL_OPERATOR);
            if(oper > UBETWEEN.getCode()) {
                builder.append(DdxzOperatorType.getTypeByCode(oper).getValue());
            } else if (oper > ULIKE.getCode()) {
                // between 操作符，含两个变量
                builder.append(DdxzOperatorType.getTypeByCode(oper).getValue())
                        .append(getNormalArgLeft(attr.getInteger(SqlConstData.SQL_DATA_TYPE)))
                        .append(attr.getString(SqlConstData.SQL_FIRST_ARG))
                        .append(getNormalArgRight(attr.getInteger(SqlConstData.SQL_DATA_TYPE)))
                        .append(SqlConstData.SQL_AND)
                        .append(getNormalArgLeft(attr.getInteger(SqlConstData.SQL_DATA_TYPE)))
                        .append(attr.getString(SqlConstData.SQL_SECOND_ARG))
                        .append(getNormalArgRight(attr.getInteger(SqlConstData.SQL_DATA_TYPE)));
            } else if (oper > UIN.getCode()) {
                builder.append(DdxzOperatorType.getTypeByCode(oper).getValue())
                        .append(getFuzzyArgLeft(attr.getInteger(SqlConstData.SQL_DATA_TYPE)))
                        .append(attr.getString(SqlConstData.SQL_SINGLE_ARG))
                        .append(getFuzzyArgRight(attr.getInteger(SqlConstData.SQL_DATA_TYPE)));
            } else if (oper > LE.getCode()) {
                builder.append(DdxzOperatorType.getTypeByCode(oper).getValue())
                        .append(SqlConstData.SQL_IN_BEGIN)
                        .append(attr.getString(SqlConstData.SQL_SINGLE_ARG))
                        .append(SqlConstData.SQL_IN_END);
            } else {
                // 其他操作符，包含一个变量
                builder.append(DdxzOperatorType.getTypeByCode(oper).getValue())
                        .append(getNormalArgLeft(attr.getInteger(SqlConstData.SQL_DATA_TYPE)))
                        .append(attr.getString(SqlConstData.SQL_SINGLE_ARG))
                        .append(getNormalArgRight(attr.getInteger(SqlConstData.SQL_DATA_TYPE)));
            }

            builder.append(DdxzAndOrType.getTypeByCode(attr.getInteger(SqlConstData.SQL_CONNECT_TYPE)).getValue());
        }

        int pos = Math.max(builder.lastIndexOf(DdxzAndOrType.AND.getValue()),
                builder.lastIndexOf(DdxzAndOrType.OR.getValue()));
        return builder.delete(pos, builder.length()).toString();
    }

    @Override
    public String getQuery(JSONArray result, JSONArray statistic, JSONArray condition, JSONArray order,
            JSONObject table, Integer apiType) {
        StringBuilder builder = new StringBuilder();
        switch (apiType) {
        case QUERY_STATEMENT:
            builder.append(getResult(result, new JSONArray()));
            break;
        case STATISTIC_STATEMENT:
            builder.append(getResult(result, statistic));
            break;
        default:
            throw new DdxzSqlException(UNKNOWN_QUERY_TYPE, apiType, QUERY_STATEMENT, STATISTIC_STATEMENT);
        }

        if (CollectionUtil.isEmpty(table)) {
            logger.error(NO_TABLE_FOUND.getMessage());
            throw new DdxzSqlException(NO_TABLE_FOUND);
        } else {
            if (StringUtil.isEmpty(table.getString(SqlConstData.KEY_SCHEMA))
                    && getDefaultSchema() == null) {
                // 达梦/oracle必须存在模式名
                throw new DdxzSqlException(SCHEMA_ERROR);
            }

            // PostgreSQL在未指定模式名时使用public模式；sqlserver在未指定模式名时使用dbo模式
            String schema = StringUtil.isEmpty(table.getString(SqlConstData.KEY_SCHEMA))
                    ? getDefaultSchema()
                    : table.getString(SqlConstData.KEY_SCHEMA);
            // 对mysql/hive无模式层级的数据库，直接使用数据表进行查询；其他数据库添加模式名进行查询
            if (StringUtil.isEmpty(schema)) {
                builder.append(SqlConstData.SQL_FROM)
                        .append(table.getString(SqlConstData.KEY_TABLE));
            } else {
                builder.append(SqlConstData.SQL_FROM)
                        .append(schema)
                        .append(".")
                        .append(table.getString(SqlConstData.KEY_TABLE));
            }
        }

        builder.append(getCondition(condition));

        if (!CollectionUtil.isEmpty(statistic)) {
            builder.append(getGroup(result));
        }

        builder.append(getOrder(order));
        return builder.toString();
    }

    @Override
    public String getArgs(JSONArray condition) {
        if(CollectionUtil.isEmpty(condition)) {
            return null;
        }

        List<String> argList = new ArrayList<>();
        condition.forEach(item -> {
            int oper = ((JSONObject)item).getInteger(SqlConstData.SQL_OPERATOR);
            if(oper > ULIKE.getCode() && oper <= UBETWEEN.getCode()) {
                argList.add(((JSONObject)item).getString(SqlConstData.SQL_SECOND_ARG));
                argList.add(((JSONObject)item).getString(SqlConstData.SQL_FIRST_ARG));
            } else if (oper <= ULIKE.getCode()) {
                argList.add(((JSONObject)item).getString(SqlConstData.SQL_SINGLE_ARG));
            }
        });

        return StringUtil.join(argList);
    }

    /**
     * 获取模式名/表名/列名的左侧特殊字符
     * @return 字符
     */
    protected abstract String getLeftQuote();

    /**
     * 获取模式名/表名/列名的右侧特殊字符
     * @return 字符
     */
    protected abstract String getRightQuote();

    /**
     * 获取数据库的缺省模式名
     * @return 字符
     */
    protected abstract String getDefaultSchema();

    private String getNormalArgLeft(int dataType) {
        switch (dataType) {
            case BIGINT:
            case INTEGER:
            case FLOAT:
                return "$";
            case STRING:
            case DATETIME:
            case TEXT:
                return "'$";
            default:
                return "";
        }
    }

    private String getNormalArgRight(int dataType){
        switch (dataType) {
            case STRING:
            case DATETIME:
            case TEXT:
                return "'";
            default:
                return "";
        }
    }

    private String getFuzzyArgLeft(int dataType) {
        switch (dataType) {
            case STRING:
            case DATETIME:
            case TEXT:
                return "'%$";
            default:
                return "$";
        }
    }

    private String getFuzzyArgRight(int dataType) {
        switch (dataType) {
            case STRING:
            case DATETIME:
            case TEXT:
                return "%'";
            default:
                return "";
        }
    }

    private static final Logger logger = LoggerFactory.getLogger(BaseSqlGenerator.class);
}
